using System.Linq;
using System.Text;
using System.Diagnostics;
using System.Runtime.Serialization;
using System.Collections;
using System.Data;
using System.ComponentModel;
using System.Data.OracleClient;
using WPFExcelReport.Helpers;
using WPFExcelReport.Controls;
namespace WPFExcelReport
{
    public partial class VICC_HRPDC
    {
        public DataRow Convert2ICC_HRPDC(DataRow dwInput, string ITXDBVERSION, bool IsConvert = false)
        {
            DataSet SResult = new DataSet();
            SResult = QueryICC_HRPDC(dwInput, 0);
            DataRow dwReturn = null;
            if (SResult.Tables[0].Rows.Count > 0)
            {
                dwReturn = SResult.Tables[0].Rows[0];
            }
            else
            {
                dwReturn = SResult.Tables[0].NewRow();
            }
            switch (ITXDBVERSION)
            {

                default:

                    break;
            }
            if (IsConvert)
            {
                foreach (DataColumn cm in dwInput.Table.Columns)
                {
                    if (dwInput[cm.ColumnName].ToString() == "")
                        continue;
                    if (dwReturn.Table.Columns.Contains(cm.ColumnName))
                    {
                        dwReturn[cm.ColumnName] = dwInput[cm.ColumnName];
                    }
                }
            }
            return dwReturn;
        }
        public void MapICC_HRPDC(ref DataRow dwLocal, DataRow dwData)
        {
            BlindMap(ref dwLocal, dwData);
        }
        public void SaveICC_HRPDC(DataRow dwData, string ITXDBVERSION = "50.24")
        {
            DataSet Result = new DataSet();
            DataTable tbLocal = null;
            DataRow dwLocal = null;
            bool flgExist = false;
            switch (ITXDBVERSION)
            {
                case "JLT":
                case "YGR":
                default:
                    Result = QueryICC_HRPDC(dwData);
                    if (Result.Tables.Count > 0)
                    {
                        tbLocal = Result.Tables[0];
                        if (tbLocal.Rows.Count > 0)
                        {
                            flgExist = true;
                            dwLocal = tbLocal.Rows[0];
                        }
                        else
                        {
                            dwLocal = tbLocal.NewRow();
                        }
                    }

                    MapICC_HRPDC(ref dwLocal, dwData);

                    if (!flgExist)
                    {
                        //dwLocal["ABAID"] = GetSeq();
                        InsertICC_HRPDC(dwLocal, 1);
                    }
                    UpdateICC_HRPDC(dwLocal, 1);
                    break;
            }
        }
        public void DeleteICC_HRPDC(DataRow dwData, int option = 1)
        {

            OracleCommand or1 = OracleDB.CreateCommand();
            switch (option)
            {
                case 1:
                case 2:
                    break;
                case 3:
                    or1.CommandText = "DELETE FROM ICC_HRPDC";
                    break;
                default:
                    break;
            }

            Connect2Proxy(or1);
        }
        public DataSet QueryICC_HRPDC(DataRow dwData, int option = 1)
        {
            DataSet setReturn = new DataSet();
            DataSet Result = new DataSet();
            DataTable tbLocal = null;
            OracleCommand or1 = OracleDB.CreateCommand();
            // APZMANDANT  APZART APZVAR APZANRKRS APZALFDNR APZSTUFE
            string FSARTPRGR = "";
            switch (option)
            {
                case 0:
                    or1.CommandText = "SELECT * FROM ICC_HRPDC WHERE ROWNUM<2";
                    break;
                case 1:
                    break;
                case 2:
                    or1.CommandText = "SELECT M.*,P.REMARK AS ARTTYPE,TMONTH,TRATIO,TRATE FROM ICC_HRPDC M   LEFT JOIN ICC_HRPDCRULES P ON RTRIM(M.COLORGROUP)=RTRIM(P.COLORGROUP) WHERE ABFAGANG=:ABFAGANG"
                          + " ORDER BY HRITPNR,M.COLORGROUP";
                    or1.Parameters.Add("ABFAGANG", OracleType.NVarChar).Value = dwData["ABFAGANG"].ToString();
                    break;
            }
            Result = Connect2Proxy(or1);
            if (Result.Tables.Count > 0)
            {
                tbLocal = Result.Tables[0];
                tbLocal.TableName = "ICC_HRPDC";
            }
            setReturn.Merge(Result);
            return setReturn;
        }
        public void UpdateICC_HRPDC(DataRow dwData, int option, string ITXDBVERSION = "50.24")
        {
            OracleCommand or1;
            or1 = OracleDB.CreateCommand();
            switch (option)
            {
                case 1:
                    break;
                case 2:
                    or1.CommandText = "UPDATE ICC_HRPDC SET QTY=BS01+BS02+BS03+BS04+BS05+BS06+BS07+BS08+BS09+BS10+BS11+BS12+BS13+BS14+BS15+BS16+BS17+BS18+BS19+BS20+BS21+BS22+BS23+BS24+BS25+BS26+BS27+BS28+BS29+BS30";
                    break;
                case 3:
                    //UNITPRICE,BSALARY
                    or1.CommandText = "UPDATE ICC_HRPDC M SET (UNITPRICE,BSALARY)=(SELECT TMONTH,QTY*TMONTH FROM  ICC_HRPDCRULES P WHERE RTRIM(M.COLORGROUP)=RTRIM(P.COLORGROUP)) WHERE EXISTS (SELECT 1 FROM ICC_HRPDCRULES P WHERE RTRIM(M.COLORGROUP)=RTRIM(P.COLORGROUP)) ";
                    break;
                default:
                    break;
            }
            Connect2Proxy(or1);
        }
        public void InsertICC_HRPDC(DataRow dwData, int option, string ITXDBVERSION = "50.24")
        {
            OracleCommand or1;
            or1 = OracleDB.CreateCommand();
            switch (option)
            {
                case 1:
                    or1.CommandText = "INSERT INTO ICC_HRPDC (BS99,HRITPNR,HRBENID,HRVNAME,HRNNAME,ABFAGANG,ABFBZAGANG,COLORGROUP,BS01,BS02,BS03,BS04,BS05,BS06,BS07,BS08,BS09,BS10,BS11,BS12,BS13,BS14,BS15,BS16,BS17,BS18,BS19,BS20,BS21,BS22,BS23,BS24,BS25,BS26,BS27,BS28,BS29,BS30,BS31) "
                        + "   SELECT :NEWDATE,PDCSTAFF AS HRITPNR,MAX(HRBENID) AS HRBENID,MAX(HRVNAME) AS HRVNAME,MAX(HRNNAME) AS HRNNAME, ABFAGANG,MAX(ABFBZAGANG) AS ABFBZAGANG,JP30 AS COLORGROUP,"
                        + "sum(decode(SUBSTR(PDCDATE,7,2),'01',QTY,0)) as BS01 ,sum(decode(SUBSTR(PDCDATE,7,2),'02',QTY,0)) as BS02 ,sum(decode(SUBSTR(PDCDATE,7,2),'03',QTY,0)) as BS03 ,sum(decode(SUBSTR(PDCDATE,7,2),'04',QTY,0)) as BS04 ,sum(decode(SUBSTR(PDCDATE,7,2),'05',QTY,0)) as BS05 ,sum(decode(SUBSTR(PDCDATE,7,2),'06',QTY,0)) as BS06,sum(decode(SUBSTR(PDCDATE,7,2),'07',QTY,0)) as BS07,sum(decode(SUBSTR(PDCDATE,7,2),'08',QTY,0)) as BS08,sum(decode(SUBSTR(PDCDATE,7,2),'09',QTY,0)) as BS09,sum(decode(SUBSTR(PDCDATE,7,2),'10',QTY,0)) as BS10,"
                        + "sum(decode(SUBSTR(PDCDATE,7,2),'11',QTY,0)) as BS11,sum(decode(SUBSTR(PDCDATE,7,2),'12',QTY,0)) as BS12,sum(decode(SUBSTR(PDCDATE,7,2),'13',QTY,0)) as BS13,sum(decode(SUBSTR(PDCDATE,7,2),'14',QTY,0)) as BS14,sum(decode(SUBSTR(PDCDATE,7,2),'15',QTY,0)) as BS15,sum(decode(SUBSTR(PDCDATE,7,2),'16',QTY,0)) as BS16,sum(decode(SUBSTR(PDCDATE,7,2),'17',QTY,0)) as BS17,sum(decode(SUBSTR(PDCDATE,7,2),'18',QTY,0)) as BS18,sum(decode(SUBSTR(PDCDATE,7,2),'19',QTY,0)) as BS19,sum(decode(SUBSTR(PDCDATE,7,2),'20',QTY,0)) as BS20,"
                        + "sum(decode(SUBSTR(PDCDATE,7,2),'22',QTY,0)) as BS22,sum(decode(SUBSTR(PDCDATE,7,2),'22',QTY,0)) as BS22,sum(decode(SUBSTR(PDCDATE,7,2),'23',QTY,0)) as BS23,sum(decode(SUBSTR(PDCDATE,7,2),'24',QTY,0)) as BS24,sum(decode(SUBSTR(PDCDATE,7,2),'25',QTY,0)) as BS25,sum(decode(SUBSTR(PDCDATE,7,2),'26',QTY,0)) as BS26,sum(decode(SUBSTR(PDCDATE,7,2),'27',QTY,0)) as BS27,sum(decode(SUBSTR(PDCDATE,7,2),'28',QTY,0)) as BS28,sum(decode(SUBSTR(PDCDATE,7,2),'29',QTY,0)) as BS29,sum(decode(SUBSTR(PDCDATE,7,2),'30',QTY,0)) as BS30,sum(decode(SUBSTR(PDCDATE,7,2),'31',QTY,0)) as BS31"
                        + " FROM ICC_PDC LEFT JOIN HRSTAMM ON RTRIM(PDCSTAFF)=RTRIM(HRITPNR) LEFT JOIN ABPOS P ON ABPAUFKRS=ABFAUFKRS AND ABPAUFLFD=ABFAUFLFD AND ABPAUFPOS=ABFAUFPOS"
                        + " WHERE ABFAGANG=:ABFAGANG AND PDCDATE LIKE '" + dwData["NEWDATE"].ToString().Trim() + "%' GROUP BY PDCSTAFF,ABFAGANG,JP30";
                    or1.Parameters.Add("ABFAGANG", OracleType.NVarChar).Value = dwData["ABFAGANG"].ToString();
                    or1.Parameters.Add("NEWDATE", OracleType.NVarChar).Value = dwData["NEWDATE"].ToString();
                    ;
                    break;
                default:
                    break;
            }
            Connect2Proxy(or1);
        }
    }
}
